#!/usr/bin/env python
import MySQLdb
import os, sys
import time
import math
import operator, struct, socket
#import Queue, threading
import GeoIP
geo = GeoIP.new(GeoIP.GEOIP_MEMORY_CACHE)

#import addrutil
#END_TIME = 1365861842    #2013/4/13 22:4:2
#END_TIME = 1397397842    #2014/4/13 22:4:2
#END_TIME = 1428933842    #2015/4/13 22:4:2
#END_TIME = 1461148192    #2016/4/20 18:29:52
END_TIME = 1465139595 #2016/6/5

class DBMgr:
  def __init__(self):
    self.conn = MySQLdb.connect(host="localhost", user="root", passwd="",
        db="hijacking", charset="utf8")
    self.cursor = self.conn.cursor()
###################PLUS NEW FUNCTION#################
  def count_alarm(self):
    TIME_QUE = [1365861842,1397397842,1428933842,1461148192]
    year_que = [2013,2014,2015,2016]
    for i in range(0,len(TIME_QUE)):
      sql = '''SELECT COUNT(*) FROM alarm WHERE time < %s '''
      self.cursor.execute(sql,TIME_QUE[i])
      cnt = self.cursor.fetchall()[0][0]
      print year_que[i],":",cnt

  def count_visitor(self):
    import datetime
    TIME_QUE = [1365861842,1397397842,1428933842,1461148192]
    time_year = [2013,2014,2015,2016]
    for i in range(0,len(TIME_QUE)):
      sql = '''SELECT COUNT(*) FROM visitor WHERE time < %s '''
      self.cursor.execute(sql, (datetime.datetime.fromtimestamp(TIME_QUE[i])))
      cnt = self.cursor.fetchall()[0][0]
      print time_year[i],":",cnt
  
  def count_reportcnt(self):
    sum = 0
    i = 0
    sql = '''select count(*) from reportcnt where all_stable !=0'''
    self.cursor.execute(sql)
    length = self.cursor.fetchall()[0][0]
    print length
    sql = '''select all_stable from reportcnt where all_stable !=0'''
    self.cursor.execute(sql)
    cnt = self.cursor.fetchall()
    for i in range(length):
       sum = sum + cnt[i][0]
       #print cnt[i][0]
    print sum
   
  def calc_pollute_AS(self):
    sum_0cnt = [0]*2000
    sum_1cnt = [0]*2000
    j = 0
    i = 0
    n = 1
    sql = '''select count(*) from signature where exists(select * from hijacking where signature.alarm_id = hijacking.alarm_id)'''
    self.cursor.execute(sql)
    count = self.cursor.fetchall()[0][0]
    sql = '''select alarm_id,cnt00,cnt01,cnt10,cnt11 from signature where exists(select * from hijacking where signature.alarm_id = hijacking.alarm_id)''' 
    self.cursor.execute(sql)
    calc = self.cursor.fetchall()
    for i in range(count-1):
        alarm_id = calc[i][0]
        print calc[i][0],calc[i][1],calc[i][2],calc[i][3],calc[i][4]
        #sum_0cnt[n] = sum_0cnt[n] + calc[i][1] + calc[i][2]
        #sum_1cnt[n] = sum_1cnt[n] + calc[i][3] + calc[i][4]
        if(alarm_id != calc[i+1][0]):
            #print calc[i][0],sum_0cnt[n],sum_1cnt[n]
            n += 1
            print n
            
            '''
            cnt00 = calc[i][1]
            cnt01 = calc[i][2]
            cnt10 = calc[i][3]
            cnt11 = calc[i][4]
            '''
        #print alarm_id,cnt00,cnt01,cnt10,cnt11
 
    '''
    calc = self.cursor.fetchall()
    for i in range(5):
      print calc[0][i]
    print calc[1]
    '''
##################################################
  def update_visitor(self):
    import datetime
    sql = ''' SELECT COUNT(*) FROM visitor WHERE time = %s '''
    self.cursor.execute(sql, (datetime.datetime.fromtimestamp(1347458476)))
    cnt = self.cursor.fetchall()[0][0]
    print "cnt:",cnt
    import random
    tims = set()
    while len(tims) < cnt:
      t = random.randint(1320336000, 1347458476)
      if t not in tims:
        tims.add(t)
    tims = sorted(tims)
    print "len(tims):",len(tims)
    sql = ''' SELECT visitor_id FROM visitor WHERE time = %s '''
    self.cursor.execute(sql, (datetime.datetime.fromtimestamp(1347458476)))
    vs = self.cursor.fetchall()
    for i in xrange(len(vs)):
      sql = ''' UPDATE visitor SET time = %s WHERE visitor_id = %s '''
      self.cursor.execute(sql, (datetime.datetime.fromtimestamp(tims[i]), vs[i][0]))
      if i % 100 == 0:
        print i, 'done'

  def get_stable_alarm_delay(self):
    sql = ''' SELECT alarm_id FROM alarm
        WHERE is_hijacking = 'Y' AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    alarms = []
    for r in self.cursor.fetchall():
      alarms.append(r[0])
    #first pollute, first alarm-first pollute, first alarm, first probe
    delay = [{}, {}, {}, {}]
    for al in alarms:
      sql = ''' SELECT elapse_time, sig, cnt00+cnt01+cnt10+cnt11 AS cnt,
          cnt00+cnt01 as cnt0 FROM signature
          WHERE alarm_id = %s ORDER BY elapse_time '''
      self.cursor.execute(sql, (al, ))
      fp = -1
      fprob = -1
      for r in self.cursor.fetchall():
        if r[3] > 0 and fp == -1:
          fp = r[0]
          if fp in delay[0]:
            delay[0][fp] += 1
          else:
            delay[0][fp] = 1
        if r[2] > 0 and fprob == -1:
          fprob = r[0]
          if fprob in delay[3]:
            delay[3][fprob] += 1
          else:
            delay[3][fprob] = 1
        if r[1] >= 0.6:
          if r[0] - fp in delay[1]:
            delay[1][r[0] - fp] += 1
          else:
            delay[1][r[0] - fp] = 1
          if r[0] in delay[2]:
            delay[2][r[0]] += 1
          else:
            delay[2][r[0]] = 1
          break
    for i in xrange(4):
      print "First ", i
      delay[i] = sorted(delay[i].iteritems(), key=operator.itemgetter(0))
      s = 0
      sv = 0.
      for p in delay[i]:
        s += p[1]
        sv += p[0] * p[1]
        if i == 1 or i == 2:
          print "%3d %3d %10lf" % (p[0], s, s * 100. / len(alarms), )
      print "All: %d, AVG %lf" % (len(alarms), sv / s)

  def get_alarm_delay(self):
    sql = ''' SELECT alarm_id FROM alarm
        WHERE is_hijacking != '' AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    alarms = []
    for r in self.cursor.fetchall():
      alarms.append(r[0])
    #first pollute, first alarm-first pollute, first alarm, first probe
    delay = [{}, {}, {}, {}]
    for al in alarms:
      sql = ''' SELECT elapse_time, sig, cnt00+cnt01+cnt10+cnt11 AS cnt,
          cnt00+cnt01 as cnt0 FROM signature
          WHERE alarm_id = %s
          ORDER BY elapse_time ''' % (al, )
      self.cursor.execute(sql)
      fp = -1
      fprob = -1
      for r in self.cursor.fetchall():
        if r[3] > 0 and fp == -1:
          fp = r[0]
          if fp in delay[0]:
            delay[0][fp] += 1
          else:
            delay[0][fp] = 1
        if r[2] > 0 and fprob == -1:
          fprob = r[0]
          if fprob in delay[3]:
            delay[3][fprob] += 1
          else:
            delay[3][fprob] = 1
        if r[1] >= 0.6:
          if r[0] - fp in delay[1]:
            delay[1][r[0] - fp] += 1
          else:
            delay[1][r[0] - fp] = 1
          if r[0] in delay[2]:
            delay[2][r[0]] += 1
          else:
            delay[2][r[0]] = 1
          break
    for i in xrange(4):
      print "First ", i
      delay[i] = sorted(delay[i].iteritems(), key=operator.itemgetter(0))
      s = 0
      sv = 0.
      for p in delay[i]:
        s += p[1]
        sv += p[0] * p[1]
        if i == 1 or i == 2:
          print "%3d %3d %10lf" % (p[0], s, s * 100. / len(alarms), )
      print "All: %d, AVG %lf" % (len(alarms), sv / s)

  def get_n_prefix(self):
    # alarm (OA+AA+PA)
    sql = ''' SELECT count(*) FROM alarm as a
        WHERE a.time <= %d AND (
          a.is_hijacking != '' OR EXISTS (SELECT * FROM alarm as b
          WHERE b.homeas = a.homeas AND b.time = a.time
            AND b.bad_path = a.bad_path AND b.is_hijacking != '')
        ) ''' % END_TIME
    self.cursor.execute(sql)
    print "Alarm(OA+AA+PA) %d" % self.cursor.fetchall()[0][0]
    # alarm(AA+PA)
    sql = ''' SELECT COUNT(*) FROM alarm
        WHERE is_hijacking != '' AND bad_path != ''
          AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    print "Alarm(AA+PA) %d" % self.cursor.fetchall()[0][0]
    # alarm PA
    sql = ''' SELECT COUNT(*) FROM alarm
        WHERE is_hijacking != '' AND bad_path LIKE '%% %% %%'
          AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    print "Alarm(PA) %d" % self.cursor.fetchall()[0][0]
    # stable alarm
    sql = ''' SELECT count(*) FROM alarm as a
        WHERE a.time <= %d AND (
          a.is_hijacking = 'Y' OR EXISTS (SELECT * FROM alarm as b
          WHERE b.homeas = a.homeas AND b.time = a.time
            AND b.bad_path = a.bad_path AND is_hijacking = 'Y')
        ) ''' % END_TIME
    self.cursor.execute(sql)
    print "Stable-Alarm(OA+AA+PA) %d" % self.cursor.fetchall()[0][0] 
    # stable alarm(AA+PA)
    sql = ''' SELECT COUNT(*) FROM alarm
        WHERE is_hijacking = 'Y' AND bad_path != ''
          AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    print "Alarm(AA+PA) %d" % self.cursor.fetchall()[0][0]
    # stable alarm PA
    sql = ''' SELECT COUNT(*) FROM alarm
        WHERE is_hijacking = 'Y' AND bad_path LIKE '%% %% %%'
          AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    print "Alarm(PA) %d" % self.cursor.fetchall()[0][0]

  def get_victim(self):
    sql = ''' SELECT homeas, bad_path, prefix_dec FROM alarm as a
        WHERE EXISTS (SELECT * FROM alarm as b
          WHERE b.is_hijacking = 'Y' AND b.time = a.time AND b.homeas = a.homeas
          AND b.bad_path = a.bad_path) '''
    self.cursor.execute(sql)
    as_alarm = [{}, {}, {}, {}]
    for r in self.cursor.fetchall():
      a = []
      if not r[1]:
        a.append(r[0])
      else:
        ases = r[1].split(' ')
        for i in ases:
          a.append(int(i))
      for i in a:
        cc = geo.country_name_by_addr(r[2][ : r[2].rindex('/')])
        if cc in as_alarm[3]:
          as_alarm[3][cc] += 1
        else:
          as_alarm[3][cc] = 1
        if cc in as_alarm[len(a)-1]:
          as_alarm[len(a)-1][cc] += 1
        else:
          as_alarm[len(a)-1][cc] = 1
        break
    as_alarm[3] = sorted(as_alarm[3].iteritems(), reverse=True, key=operator.itemgetter(1))
    for kv in as_alarm[3]:
      print "%40s %3d " % (kv[0], kv[1]),
      for i in xrange(3):
        print "%3d " % as_alarm[i].get(kv[0], 0),
      print

  def get_attacker(self):
    sql = ''' SELECT oldhomeas, prefix_dec FROM alarm
        WHERE is_hijacking = 'Y' '''
    self.cursor.execute(sql)
    as_alarm = [{}, {}, {}, {}]
    for r in self.cursor.fetchall():
      a = []
      if r[0] != 0:
        a.append(r[0])
      else:
        ases = r[1].split(' ')
        for i in ases:
          a.append(int(i))
      for i in a:
        cc = geo.country_name_by_addr(r[2][ : r[2].rindex('/')])
        if cc in as_alarm[3]:
          as_alarm[3][cc] += 1
        else:
          as_alarm[3][cc] = 1
        if cc in as_alarm[len(a)-1]:
          as_alarm[len(a)-1][cc] += 1
        else:
          as_alarm[len(a)-1][cc] = 1
    as_alarm[3] = sorted(as_alarm[3].iteritems(), reverse=True, key=operator.itemgetter(1))
    for kv in as_alarm[3]:
      print "%40s %3d " % (kv[0], kv[1]),
      for i in xrange(3):
        print "%3d " % as_alarm[i].get(kv[0], 0),
      print

  def get_all_prefix_length(self):
    class TrieNode:
      def __init__(self):
        self.sons = [None, None]
        self.allHomes = None  # set: {homeAS}

    class Trie:
      def __init__(self):
        self.cidx = {'0': 0, '1': 1}
        self.cset = "01"
        self.root = TrieNode()
      def clear(self):
        self.root = TrieNode()
        self.db.close()
      def walk_down_trie(self, pfx_length, ipbeg_bin):
        cur = self.root
        for ip in xrange(len(ipbeg_bin)):
          if pfx_length <= 0:
            break
          for jp in xrange(7, -1, -1):
            if pfx_length <= 0:
              break
            pfx_length -= 1
            if ord(ipbeg_bin[ip]) & (1 << jp):
              c = '1'
            else:
              c = '0'
            # for each c
            if cur.sons[self.cidx[c]] is None:
              cur.sons[self.cidx[c]] = TrieNode()
            cur = cur.sons[self.cidx[c]]
        cur.allHomes = True
      def walk_all(self, l, stat, cur):
        if cur.allHomes:
          if l in stat[0]:
            stat[0][l] += 1
          else:
            stat[0][l] = 1
          if (not cur.sons[0]) and (not cur.sons[1]):
            if l in stat[1]:
              stat[1][l] += 1
            else:
              stat[1][l] = 1
        if cur.sons[0]:
          self.walk_all(l+1, stat, cur.sons[0])
        if cur.sons[1]:
          self.walk_all(l+1, stat, cur.sons[1])

    def uint2bin(u):
      ''' convert unsigned int to binary format '''
      return struct.pack('>I', u)

    trie = Trie()
    stat = [{}, {}]
    n_cur = 0
    ssconn = MySQLdb.connect(host="localhost", user="root", passwd="",
        db="hijacking", charset="utf8")   
    sscursor = ssconn.cursor(MySQLdb.cursors.SSCursor)
    sql = ''' SELECT distinct ipbeg, ipend FROM origin_oldv4 '''
    sscursor.execute(sql)
    for r in sscursor:
      delta = (r[1] - r[0])
      l = 0
      while delta >= 1:
        l += 1
        delta >>= 1
      trie.walk_down_trie(33-l, uint2bin(r[0]))
      n_cur += 1
      if n_cur % 10000 == 0:
        print "n_cur %d processed" % n_cur
    trie.walk_all(0, stat, trie.root)
    stat[0] = sorted(stat[0].iteritems(), key=operator.itemgetter(0))
    s = [0, 0]
    for kv in stat[0]:
      n_longest = stat[1].get(kv[0], 0)
      s[0] += kv[1]
      s[1] += n_longest
      print "%3d %6d %6d %10lf" % (
          kv[0], n_longest, kv[1], n_longest * 100.0 / kv[1], )
    print "All %6d %6d %10lf" % (
        s[1], s[0], s[1] * 100.0 / s[0], )

  def get_alarm_prefix_length(self):
    def out(stat):
      s = [0, 0]
      for i in xrange(8, 31):
        n_long = stat[0].get(i, 1)
        n_all = stat[1].get(i, 0) + n_long
        s[0] += n_long
        s[1] += n_all
        if n_all:
          print "%3d %6d %6d %10lf" % (
              #i, n_long, n_all, n_long * 100.0 / n_all, )
              i, s[0], s[1], s[0] * 100.0 / s[1], )
        else:
          print "%3d %6d %6d %10lf" % (i, 0, 0, 0, )
      print "All %6d %6d %10lf" % (
        s[0], s[1], s[0] * 100.0 / s[1], )
    lus = ["(1,4,6,9,10,13,14,17)", "(2,3,7,8,11,12,15,16)"]
    # anomalies
    stat = [{}, {}]
    i = 0
    for lu in lus:
      sql = ''' SELECT pfx_length, COUNT(pfx_length) FROM alarm
          WHERE type in %s AND time <= %d
          GROUP BY pfx_length ORDER BY pfx_length ''' % (lu, END_TIME)
      self.cursor.execute(sql)
      for r in self.cursor.fetchall():
        stat[i][r[0]] = r[1]
      i += 1
    print "Anomaly:"
    out(stat)
    print
    #
    # alarms
    stat = [{}, {}]
    i = 0
    for lu in lus:
      sql = ''' SELECT pfx_length, COUNT(pfx_length) FROM alarm
          WHERE type in %s AND time <= %d AND is_hijacking != ''
          GROUP BY pfx_length ORDER BY pfx_length ''' % (lu, END_TIME)
      self.cursor.execute(sql)
      for r in self.cursor.fetchall():
        stat[i][r[0]] = r[1]
      i += 1
    print "Alarm:"
    out(stat)
    print
    #
    # stable alarms
    stat = [{}, {}]
    i = 0
    for lu in lus:
      sql = ''' SELECT pfx_length, COUNT(pfx_length) FROM alarm
          WHERE type in %s AND is_hijacking = 'Y' AND time <= %d
          GROUP BY pfx_length ORDER BY pfx_length ''' % (lu, END_TIME)
      self.cursor.execute(sql)
      for r in self.cursor.fetchall():
        stat[i][r[0]] = r[1]
      i += 1
    print "Stable alarm:"
    out(stat)
    print

  def get_alarm_prefix_type(self):
    def output(rows):
      # alarm path type
      grp = [0, 4, 3, 2, 1, 100, 9, 8, 7, 6,
          13, 12, 11, 10, 17, 16, 15, 14,
          21, 20, 19, 18, 25, 24, 23, 22,]
      type_count = [0] * 26
      for r in rows:
        if r[1].count(' ') < 2:
          type_count[r[3]] += 1
        else:
          type_count[r[3]+8] += 1
      s_6789 = 0
      for i in xrange(6, 10):
        s_6789 += type_count[i]
      for i in xrange(6, 10):
        type_count[i] += int(type_count[5] * type_count[i] * 1.0 / s_6789)
      for i in xrange(26):
        print "%3d %6d" % (i, type_count[i])
      print
      for i in xrange(26):
        if i >= grp[i]:
          print "%3d %6d" % (i, type_count[i] + type_count[grp[i]])
    
    print "Anomalies:"
    sql = ''' SELECT time, bad_path, homeas, type FROM alarm
        WHERE time <= %d
        GROUP BY time, bad_path, homeas ''' % END_TIME
    self.cursor.execute(sql)
    output(self.cursor.fetchall())
    #
    print "Alarms:"
    sql = ''' SELECT time, bad_path, homeas, type FROM alarm
        WHERE time <= %d AND is_hijacking != '' ''' % END_TIME
    self.cursor.execute(sql)
    output(self.cursor.fetchall())
    #
    print "Stable Alarms:"
    sql = ''' SELECT time, bad_path, homeas, type FROM alarm
        WHERE is_hijacking = 'Y' AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    output(self.cursor.fetchall())
    
  def get_stable_alarm_time_type(self, fig='cdf', duration='day'):
    # alarm path type
    # time-group
    sql = ''' SELECT distinct time, bad_path, homeas FROM alarm
        WHERE is_hijacking = 'Y' AND time <= %d ''' % END_TIME
    self.cursor.execute(sql)
    time_alarm = [{}, {}, {}, {}]
    for r in self.cursor.fetchall():
      if duration == 'day':
        ym = int(time.strftime('%y%m%d', time.gmtime(int(r[0]))))
      elif duration == 'week':
        ym = int(time.strftime('%y%W', time.gmtime(int(r[0]))))
      elif duration == 'month':
        ym = int(time.strftime('%y%m', time.gmtime(int(r[0]))))
      else:
        return
      if ym in time_alarm[3]:
        time_alarm[3][ym] += 1
      else:
        time_alarm[3][ym] = 1
      tp = r[1].count(' ')
      if ym in time_alarm[tp]:
        time_alarm[tp][ym] += 1
      else:
        time_alarm[tp][ym] = 1
    time_alarm[3] = sorted(
        time_alarm[3].iteritems(), key=operator.itemgetter(0))
    print "All stable alarms:"
    cnt = [0, 0, 0, 0]
    for k, v in time_alarm[3]:
      if fig == 'cdf':
        cnt[3] += v
      elif fig == 'pdf':
        cnt[3] = v
      for i in xrange(3):
        if i == 2:
          if duration == 'day' and k <= 120109:
            continue
          elif duration == 'week' and k <= 1201:
            continue
          elif duration == 'month' and k <= 1112:
            continue
        if k in time_alarm[i]:
          if fig == 'cdf':
            cnt[i] += time_alarm[i][k]
          elif fig == 'pdf':
            cnt[i] = time_alarm[i][k]
      print "%3s %6d %6d %6d %6d" % (k, cnt[0], cnt[1], cnt[2], cnt[3])

  def get_salarm_vs_neye(self):
    sql = ''' SELECT MAX(cnt00+cnt01) FROM alarm, signature
        WHERE alarm.alarm_id = signature.alarm_id AND is_hijacking = 'Y'
          AND time <= %d
        GROUP BY signature.alarm_id ''' % END_TIME
    self.cursor.execute(sql)
    neye = {}
    total = 0
    for r in self.cursor.fetchall():
      total += 1
      if r[0] in neye:
        neye[r[0]] += 1
      else:
        neye[r[0]] = 1
    neye = sorted(neye.iteritems(), key=operator.itemgetter(0))
    cnt = 0
    for kv in neye:
      cnt += kv[1]
      print "%2d %3d %10lf" % (kv[0], cnt, cnt * 100.0 / total)
    print 'All: %d' % total
    
  def get_alarm_time_type(self, fig='cdf', duration='day'):
    # alarm path type
    # time-group
    sql = ''' SELECT distinct time, bad_path, homeas FROM alarm
        WHERE time <= %d AND is_hijacking != '' ''' % END_TIME
    self.cursor.execute(sql)
    time_alarm = [{}, {}, {}, {}]
    for r in self.cursor.fetchall():
      if duration == 'day':
        ym = int(time.strftime('%y%m%d', time.gmtime(int(r[0]))))
      elif duration == 'week':
        ym = int(time.strftime('%y%W', time.gmtime(int(r[0]))))
      elif duration == 'month':
        ym = int(time.strftime('%y%m', time.gmtime(int(r[0]))))
      else:
        return
      if ym in time_alarm[3]:
        time_alarm[3][ym] += 1
      else:
        time_alarm[3][ym] = 1
      tp = r[1].count(' ')
      if ym in time_alarm[tp]:
        time_alarm[tp][ym] += 1
      else:
        time_alarm[tp][ym] = 1
    time_alarm[3] = sorted(
        time_alarm[3].iteritems(), key=operator.itemgetter(0))
    cnt = [0, 0, 0, 0]
    print "All alarms:"
    for k, v in time_alarm[3]:
      if fig == 'cdf':
        cnt[3] += v
      elif fig == 'pdf':
        cnt[3] = v
      for i in xrange(3):
        if i == 2:
          if duration == 'day' and k <= 120109:
            continue
          elif duration == 'week' and k <= 1201:
            continue
          elif duration == 'month' and k <= 1112:
            continue
        if k in time_alarm[i]:
          if fig == 'cdf':
            cnt[i] += time_alarm[i][k]
          elif fig == 'pdf':
            cnt[i] = time_alarm[i][k]
      print "%3s %6d %6d %6d %6d" % (k, cnt[0], cnt[1], cnt[2], cnt[3])
    
  def get_alarm_vs_neye(self):
    sql = ''' SELECT MAX(cnt00+cnt01) FROM alarm, signature
        WHERE alarm.alarm_id = signature.alarm_id AND sig >= 0.6
          AND is_hijacking != '' AND time <= %d
        GROUP BY signature.alarm_id ''' % END_TIME
    self.cursor.execute(sql)
    neye = {}
    total = 0
    for r in self.cursor.fetchall():
      total += 1
      if r[0] in neye:
        neye[r[0]] += 1
      else:
        neye[r[0]] = 1
    neye = sorted(neye.iteritems(), key=operator.itemgetter(0))
    cnt = 0
    for kv in neye:
      cnt += kv[1]
      print "%2d %3d %10lf" % (kv[0], cnt, cnt * 100.0 / total)
    print 'All: %d' % total

  def get_anomaly_time_type(self, fig='cdf', duration='day'):
    # anomaly path type
    # time-group
    sql = ''' SELECT distinct time, bad_path, homeas FROM alarm
        WHERE time <= %d AND EXISTS (SELECT * FROM signature
          WHERE signature.alarm_id = alarm.alarm_id) ''' % END_TIME
    self.cursor.execute(sql)
    time_anomaly = [{}, {}, {}, {}]
    for r in self.cursor.fetchall():
      if duration == 'day':
        ym = int(time.strftime('%y%m%d', time.gmtime(int(r[0]))))
      elif duration == 'week':
        ym = int(time.strftime('%y%W', time.gmtime(int(r[0]))))
      elif duration == 'month':
        ym = int(time.strftime('%y%m', time.gmtime(int(r[0]))))
      else:
        return
      if ym in time_anomaly[3]:
        time_anomaly[3][ym] += 1
      else:
        time_anomaly[3][ym] = 1
      tp = r[1].count(' ')
      if ym in time_anomaly[tp]:
        time_anomaly[tp][ym] += 1
      else:
        time_anomaly[tp][ym] = 1
    time_anomaly[3] = sorted(
        time_anomaly[3].iteritems(), key=operator.itemgetter(0))
    cnt = [0, 0, 0, 0]
    print "All anomalies:"
    for k, v in time_anomaly[3]:
      if fig == 'cdf':
        cnt[3] += v
      elif fig == 'pdf':
        cnt[3] = v
      for i in xrange(3):
        if i == 2:
          if duration == 'day' and k <= 120109:
            continue
          elif duration == 'week' and k <= 1201:
            continue
          elif duration == 'month' and k <= 1112:
            continue
        if k in time_anomaly[i]:
          if fig == 'cdf':
            cnt[i] += time_anomaly[i][k]
          elif fig == 'pdf':
            cnt[i] = time_anomaly[i][k]
      print "%3s %6d %6d %6d %6d" % (k, cnt[0], cnt[1], cnt[2], cnt[3])
    
  def get_fnr_vs_mu(self, crit='rpki', T=1):
    if T == 10:
      for mu in xrange(40, 101):
        self.get_fnr_vs_s(crit, mu / 100., T)
      return
    if crit == 'rpki':
      sql = ''' SELECT * FROM alarm
          WHERE rpki = 'N' AND time <= %d AND is_hijacking != '' ''' % END_TIME
    elif crit == 'whois':
      sql = ''' SELECT * FROM alarm
          WHERE whois_origin = 1 AND time <= %d AND is_hijacking != '' ''' % END_TIME
    self.cursor.execute(sql)
    crits = self.cursor.fetchall()
    print "Sum: %d" % len(crits)
    for r in xrange(25, 101):
      if crit == 'rpki':
        sql = ''' SELECT * FROM alarm WHERE rpki = 'N' AND EXISTS (
            SELECT * FROM signature
              WHERE signature.alarm_id = alarm.alarm_id AND sig >= %s ) '''
      elif crit == 'whois':
        sql = ''' SELECT * FROM alarm WHERE whois_origin = 1 AND EXISTS (
            SELECT * FROM signature
              WHERE signature.alarm_id = alarm.alarm_id AND sig >= %s ) '''
      self.cursor.execute(sql, (r * 1.0 / 100, ))
      rr = self.cursor.fetchall()
      print "%10lf %3d %10lf" % (r * 1.0 / 100, len(rr), len(rr) * 100. /
          (len(crits)+1))
      if not rr:
        break

  def get_inconsis_eye_in_AS(self):
    MaxServer = 512
    MaxTime = 120
    def get_fing_eye(alarm_time, probes, routes, asn_eyes):
      # flush probe, route
      probe_matrix = [None for i in xrange(MaxServer)]
      route_matrix= [None for i in xrange(MaxServer)]
      for p in probes:
        if p[0] >= alarm_time and p[0] - alarm_time < MaxTime:
          if not probe_matrix[p[1]]:
            probe_matrix[p[1]] = [-1 for j in xrange(MaxTime)]
          probe_matrix[p[1]][p[0] - alarm_time] = p[2]
      for r in routes:
        if r[0] >= alarm_time and r[0] - alarm_time < MaxTime:
          if not route_matrix[r[1]]:
            route_matrix[r[1]] = [-1 for j in xrange(MaxTime)]
          route_matrix[r[1]][r[0] - alarm_time] = r[2]
      srv_ids = [i for i in xrange(MaxServer)
          if probe_matrix[i] and route_matrix[i]]
      # prepare
      for i in srv_ids:
        presp = -1
        pisold = -1
        for j in xrange(0, MaxTime):
          if probe_matrix[i][j] == -1:
            probe_matrix[i][j] = presp
          if route_matrix[i][j] == -1:
            route_matrix[i][j] = pisold
          presp = probe_matrix[i][j]
          pisold = route_matrix[i][j]
      # in consis
      total = 0
      incon = 0
      for asn, eyes in asn_eyes.iteritems():
        for i in xrange(len(eyes)):
          ei = eyes[i]
          if not route_matrix[ei]:
            continue
          for t in xrange(MaxTime):
            if route_matrix[ei][t] == -1:
              continue
            tag = False
            for j in xrange(len(eyes)):
              if i == j:
                continue
              ej = eyes[j]
              if (not route_matrix[ej]) or (route_matrix[ej][t] == -1):
                continue
              tag = True
              if route_matrix[ei][t] + route_matrix[ej][t] == 1:
                incon += 1
                break
            if tag:
              total += 1
      return total, incon
    asn_eyes = {}
    sql = ''' SELECT asn, eye_id FROM eye '''
    self.cursor.execute(sql)
    for e in self.cursor.fetchall():
      if e[0] not in asn_eyes:
        asn_eyes[e[0]] = []
      asn_eyes[e[0]].append(e[1])
    sql = ''' SELECT alarm_id, time FROM alarm
        WHERE time <= %d AND EXISTS (
          SELECT * FROM signature
          WHERE signature.alarm_id = alarm.alarm_id ) ''' % END_TIME
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    total, incon = 0, 0
    for i in xrange(len(alarms)):
      sql = ''' SELECT time, srv_id, response FROM probe WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      probes = self.cursor.fetchall()
      sql = ''' SELECT time, srv_id, isoldhome FROM route WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      routes = self.cursor.fetchall()
      ta, tic = get_fing_eye(alarms[i][1], probes, routes, asn_eyes)
      total += ta
      incon += tic
      if i % 1000 == 0:
        print 'DEBUG: ', i
    print 'TOTAL  INCONSIS  ratio'
    print total, incon, incon * 1.0 / total

  def get_inconsis_CD(self):
    MaxServer = 512
    MaxTime = 120
    def get_fing_eye(alarm_time, probes, routes):
      # flush probe, route
      probe_matrix = [None for i in xrange(MaxServer)]
      route_matrix= [None for i in xrange(MaxServer)]
      for p in probes:
        if p[0] >= alarm_time and p[0] - alarm_time < MaxTime:
          if not probe_matrix[p[1]]:
            probe_matrix[p[1]] = [-1 for j in xrange(MaxTime)]
          probe_matrix[p[1]][p[0] - alarm_time] = p[2]
      for r in routes:
        if r[0] >= alarm_time and r[0] - alarm_time < MaxTime:
          if not route_matrix[r[1]]:
            route_matrix[r[1]] = [-1 for j in xrange(MaxTime)]
          route_matrix[r[1]][r[0] - alarm_time] = r[2]
      srv_ids = [i for i in xrange(MaxServer)
          if probe_matrix[i] and route_matrix[i]]
      # prepare
      for i in srv_ids:
        presp = -1
        pisold = -1
        for j in xrange(0, MaxTime):
          if probe_matrix[i][j] == -1:
            probe_matrix[i][j] = presp
          if route_matrix[i][j] == -1:
            route_matrix[i][j] = pisold
          presp = probe_matrix[i][j]
          pisold = route_matrix[i][j]
      # coff
      eye_max_sig = [0.] * len(srv_ids)
      counter = [
          [[0 for k in xrange(2)] for j in xrange(2)]
            for i in xrange(MaxTime)
        ]
      coff = [0.0 for j in xrange(MaxTime)]
      for j in xrange(0, MaxTime):
        sum_xy = sum_x = sum_y = sum_xx = sum_yy = 0.
        cnt = 0
        for i in srv_ids:
          if probe_matrix[i][j] == -1 or route_matrix[i][j] == -1:
            continue
          counter[j][ route_matrix[i][j] ][ probe_matrix[i][j] ] += 1
          sum_xy += probe_matrix[i][j] * route_matrix[i][j]
          sum_xx += probe_matrix[i][j] * probe_matrix[i][j]
          sum_yy += route_matrix[i][j] * route_matrix[i][j]
          sum_x += probe_matrix[i][j]
          sum_y += route_matrix[i][j]
          cnt += 1
        if cnt == 0:
          continue
        coff[j] = math.sqrt(
            (sum_xx / cnt - (sum_x * sum_x) / (cnt * cnt)) *
            (sum_yy / cnt - (sum_y * sum_y) / (cnt * cnt)))
        if math.fabs(coff[j]) < 1E-10:
          coff[j] = 0.
        else:
          coff[j] = ((sum_xy / cnt) - (sum_x * sum_y) / (cnt * cnt)) / coff[j]
      # in consis
      total = 0
      incon = 0
      for i in srv_ids:
        for j in xrange(0, MaxTime):
          if coff[j] < 0.6 or route_matrix[i][j] == -1 or probe_matrix[i][j] == -1:
            continue
          total += 1
          if route_matrix[i][j] != probe_matrix[i][j]:
            incon += 1
      return total, incon
    sql = ''' SELECT alarm_id, time FROM alarm
        WHERE time <= %d AND is_hijacking != '' AND EXISTS (
          SELECT * FROM signature
          WHERE signature.alarm_id = alarm.alarm_id ) ''' % END_TIME
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    total, incon = 0, 0
    for i in xrange(len(alarms)):
      sql = ''' SELECT time, srv_id, response FROM probe WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      probes = self.cursor.fetchall()
      sql = ''' SELECT time, srv_id, isoldhome FROM route WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      routes = self.cursor.fetchall()
      ta, tic = get_fing_eye(alarms[i][1], probes, routes)
      total += ta
      incon += tic
    print 'TOTAL  INCONSIS  ratio'
    print total, incon, incon * 1.0 / total

  def get_zerod_Ft(self):
    MaxServer = 512
    MaxTime = 120
    def get_fing_eye(alarm_time, probes, routes, eye_asn):
      # flush probe, route
      probe_matrix = [None for i in xrange(MaxServer)]
      route_matrix= [None for i in xrange(MaxServer)]
      for p in probes:
        if p[0] >= alarm_time and p[0] - alarm_time < MaxTime:
          if not probe_matrix[p[1]]:
            probe_matrix[p[1]] = [-1 for j in xrange(MaxTime)]
          probe_matrix[p[1]][p[0] - alarm_time] = p[2]
      for r in routes:
        if r[0] >= alarm_time and r[0] - alarm_time < MaxTime:
          if not route_matrix[r[1]]:
            route_matrix[r[1]] = [-1 for j in xrange(MaxTime)]
          route_matrix[r[1]][r[0] - alarm_time] = r[2]
      srv_ids = [i for i in xrange(MaxServer)
          if probe_matrix[i] and route_matrix[i]]
      # prepare
      for i in srv_ids:
        presp = -1
        pisold = -1
        for j in xrange(0, MaxTime):
          if probe_matrix[i][j] == -1:
            probe_matrix[i][j] = presp
          if route_matrix[i][j] == -1:
            route_matrix[i][j] = pisold
          presp = probe_matrix[i][j]
          pisold = route_matrix[i][j]
      # coff
      total = 0
      zero = 0
      zero_counter = [0, 0, 0, 0]
      eye_max_sig = [0.] * len(srv_ids)
      counter = [
          [[0 for k in xrange(2)] for j in xrange(2)]
            for i in xrange(MaxTime)
        ]
      coff = [0.0 for j in xrange(MaxTime)]
      max_asn = 0
      for j in xrange(0, MaxTime):
        sum_xy = sum_x = sum_y = sum_xx = sum_yy = 0.
        cnt = 0
        num_asn = set()
        for i in srv_ids:
          if probe_matrix[i][j] == -1 or route_matrix[i][j] == -1:
            continue
          counter[j][ route_matrix[i][j] ][ probe_matrix[i][j] ] += 1
          sum_xy += probe_matrix[i][j] * route_matrix[i][j]
          sum_xx += probe_matrix[i][j] * probe_matrix[i][j]
          sum_yy += route_matrix[i][j] * route_matrix[i][j]
          sum_x += probe_matrix[i][j]
          sum_y += route_matrix[i][j]
          cnt += 1
          num_asn.add(eye_asn[i])
        if cnt == 0:
          continue
        if len(num_asn) > max_asn:
          max_asn = len(num_asn)
        if sum_x == 0:
          zero_counter[0] += 1
        elif sum_x == cnt:
          zero_counter[1] += 1
        if sum_y == 0:
          zero_counter[2] += 1
        elif sum_y == cnt:
          zero_counter[3] += 1
        coff[j] = math.sqrt(
            (sum_xx / cnt - (sum_x * sum_x) / (cnt * cnt)) *
            (sum_yy / cnt - (sum_y * sum_y) / (cnt * cnt)))
        total += 1
        if math.fabs(coff[j]) < 1E-10:
          zero += 1
      if max_asn < 20:
        return (0, 0, (0, 0, 0, 0))
      else:
        return (
            1, (1 if zero == total else 0),
            (
              (1 if zero_counter[0] == total else 0),
              (1 if zero_counter[1] == total else 0),
              (1 if zero_counter[2] == total else 0),
              (1 if zero_counter[3] == total else 0),
            )
          )
    # eye ASN
    sql = ''' SELECT eye_id, asn FROM eye '''
    self.cursor.execute(sql)
    eyes = self.cursor.fetchall()
    print '#eyes:', len(eyes)
    eye_asn = [0] * 500
    for e in eyes:
      eye_asn[e[0]] = e[1]
    #
    sql = ''' SELECT alarm_id, time FROM alarm
        WHERE time <= %d AND time >= %d AND EXISTS (
          SELECT * FROM signature
          WHERE signature.alarm_id = alarm.alarm_id ) ''' % (
              END_TIME, END_TIME - 30 * 24 * 3600)
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    total, zero, zero_counter = 0, 0, [0, 0, 0, 0]
    for i in xrange(len(alarms)):
      sql = ''' SELECT time, srv_id, response FROM probe WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      probes = self.cursor.fetchall()
      sql = ''' SELECT time, srv_id, isoldhome FROM route WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      routes = self.cursor.fetchall()
      ta, tz, tzc = get_fing_eye(alarms[i][1], probes, routes, eye_asn)
      total += ta
      zero += tz
      for j in xrange(len(tzc)):
        zero_counter[j] += tzc[j]
      if i % 1000 == 0:
        print 'DEBUG: ', i
    print '     TOTAL  ZERO  ratio'
    print 'D=0', total, zero_counter[0], zero_counter[0] * 1.0 / total
    print 'D=1', total, zero_counter[1], zero_counter[1] * 1.0 / total
    print 'C=0', total, zero_counter[2], zero_counter[2] * 1.0 / total
    print 'C=1', total, zero_counter[3], zero_counter[3] * 1.0 / total
    print 'Ft=0', total, zero, zero * 1.0 / total

  def get_fnr_vs_eye(self, crit='rpki', T=1):
    MaxServer = 256
    MaxTime = 120
    MaxEye = 40
    def get_fing_eye(alarm_time, probes, routes):
      # flush probe, route
      probe_matrix = [None for i in xrange(MaxServer)]
      route_matrix= [None for i in xrange(MaxServer)]
      for p in probes:
        if p[0] >= alarm_time and p[0] - alarm_time < MaxTime:
          if not probe_matrix[p[1]]:
            probe_matrix[p[1]] = [-1 for j in xrange(MaxTime)]
          probe_matrix[p[1]][p[0] - alarm_time] = p[2]
      for r in routes:
        if r[0] >= alarm_time and r[0] - alarm_time < MaxTime:
          if not route_matrix[r[1]]:
            route_matrix[r[1]] = [-1 for j in xrange(MaxTime)]
          route_matrix[r[1]][r[0] - alarm_time] = r[2]
      srv_ids = [i for i in xrange(MaxServer)
          if probe_matrix[i] and route_matrix[i]]
      # prepare
      for i in srv_ids:
        presp = -1
        pisold = -1
        for j in xrange(0, MaxTime):
          if probe_matrix[i][j] == -1:
            probe_matrix[i][j] = presp
          if route_matrix[i][j] == -1:
            route_matrix[i][j] = pisold
          presp = probe_matrix[i][j]
          pisold = route_matrix[i][j]
      # coff
      eye_max_sig = [0.] * MaxEye
      for neye in xrange(5, MaxEye):
        counter = [
            [[0 for k in xrange(2)] for j in xrange(2)]
              for i in xrange(MaxTime)
          ]
        coff = [0.0 for j in xrange(MaxTime)]
        for j in xrange(0, MaxTime):
          sum_xy = sum_x = sum_y = sum_xx = sum_yy = 0.
          cnt = 0
          for i in srv_ids[:neye]:
            if probe_matrix[i][j] == -1 or route_matrix[i][j] == -1:
              continue
            counter[j][ route_matrix[i][j] ][ probe_matrix[i][j] ] += 1
            sum_xy += probe_matrix[i][j] * route_matrix[i][j]
            sum_xx += probe_matrix[i][j] * probe_matrix[i][j]
            sum_yy += route_matrix[i][j] * route_matrix[i][j]
            sum_x += probe_matrix[i][j]
            sum_y += route_matrix[i][j]
            cnt += 1
          if cnt == 0:
            continue
          coff[j] = math.sqrt(
              (sum_xx / cnt - (sum_x * sum_x) / (cnt * cnt)) *
              (sum_yy / cnt - (sum_y * sum_y) / (cnt * cnt)))
          if math.fabs(coff[j]) < 1E-10:
            coff[j] = 0.
          else:
            coff[j] = ((sum_xy / cnt) - (sum_x * sum_y) / (cnt * cnt)) / coff[j]
        # insert
        coff.sort(reverse=True)
        # is a hijacking
        max_sig = coff[T-1]
        eye_max_sig[neye] = max_sig
      return eye_max_sig

    if crit == 'rpki':
      sql = ''' SELECT alarm_id, time FROM alarm
          WHERE rpki = 'N' AND time <= %d AND EXISTS (
            SELECT * FROM signature
            WHERE signature.alarm_id = alarm.alarm_id ) ''' % END_TIME
    elif crit == 'whois':
      sql = ''' SELECT alarm_id, time FROM alarm
          WHERE whois_origin = 1 AND time <= %d AND EXISTS (
            SELECT * FROM signature
            WHERE signature.alarm_id = alarm.alarm_id ) ''' % END_TIME
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    max_sig = [
          [0 for j in xrange(MaxEye)] for i in xrange(len(alarms))]
    mu = [0.6, ]
    nsig = [
          [0 for j in xrange(len(mu))] for i in xrange(MaxEye)]
    for i in xrange(len(alarms)):
      sql = ''' SELECT time, srv_id, response FROM probe WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      probes = self.cursor.fetchall()
      sql = ''' SELECT time, srv_id, isoldhome FROM route WHERE alarm_id = %s '''
      self.cursor.execute(sql, (alarms[i][0], ))
      routes = self.cursor.fetchall()
      max_sig[i] = get_fing_eye(alarms[i][1], probes, routes)
      for neye in xrange(5, MaxEye):
        for j in xrange(len(mu)):
          if max_sig[i][neye] >= mu[j]:
            nsig[neye][j] += 1
    print "Sum: %d" % len(alarms)
    for neye in xrange(5, MaxEye):
      print "%2d" % neye,
      for j in xrange(len(mu)):
        print " %3lf %3d %10lf" % (
            mu[j], nsig[neye][j], nsig[neye][j] * 100.0 / len(alarms)),
      print

  def get_not_reply(self):
    # cat ../log/201*.log | grep live | grep ip | awk '{print $8, $NF}' | python do.py
    is_alive = {}
    total = 0
    for l in sys.stdin:
      total += 1
      c = l.split(' ')
      try:
        aid = int(c[0])
        alive = int(c[1])
      except:
        print l
      if not alive:
        is_alive[aid] = alive
    cnt = 0
    for k, v in is_alive.iteritems():
      sql = ''' SELECT * FROM signature
          WHERE cnt01+cnt11 > 0 AND alarm_id = %s '''
      self.cursor.execute(sql, (k, ))
      rs = self.cursor.fetchall()
      if not rs:
        cnt += 1
    print "%6d %6d %10lf %6d" % (
        cnt, len(is_alive), cnt * 100. / len(is_alive), total)

  def get_salarm_finger_distr(self, type='pdf'):
    sql = ''' SELECT MAX(sig), bad_path FROM alarm, signature
        WHERE alarm.alarm_id = signature.alarm_id AND sig >= 0.6
          AND is_hijacking = 'Y' AND time <= %d
        GROUP BY alarm.alarm_id
        ORDER BY MAX(sig) DESC
      ''' % END_TIME
    self.cursor.execute(sql)
    s = 0
    if type == 'pdf':
      cnt = [0] * 100
    elif type == 'cdf':
      cnt = {}
    for r in self.cursor.fetchall():
      s += 1
      if type == 'pdf':
        if r[0] == 1:
          cnt[19] += 1
        else:
          cnt[int(r[0] * 100 / 5)] += 1
      elif type == 'cdf':
        if r[0] in cnt:
          cnt[r[0]] += 1
        else:
          cnt[r[0]] = 1
    if type == 'pdf':
      for i in xrange(20):
        print "0.%d %6d %10lf" % (i*5*10 + 25, cnt[i], cnt[i] * 100.0 / s)
    elif type == 'cdf':
      cnt = sorted(cnt.iteritems(), key=operator.itemgetter(0))
      c = 0
      for kv in cnt:
        c += kv[1]
        print "%10lf %6d %10lf" % (kv[0], c, c * 100.0 / s)
    print "Sum: %d" % s

  def get_alarm_finger_distr(self, type='pdf'):
    sql = ''' SELECT MAX(sig), bad_path FROM alarm, signature
        WHERE alarm.alarm_id = signature.alarm_id
        GROUP BY alarm.alarm_id
        ORDER BY MAX(sig) DESC
      '''
    self.cursor.execute(sql)
    s = 0
    if type == 'pdf':
      cnt = [0] * 100
    elif type == 'cdf':
      cnt = {}
    for r in self.cursor.fetchall():
      s += 1
      if type == 'pdf':
        cnt[int(r[0] * 100 / 5)] += 1
      elif type == 'cdf':
        if r[0] in cnt:
          cnt[r[0]] += 1
        else:
          cnt[r[0]] = 1
    if type == 'pdf':
      for i in xrange(21):
        print "0.%d %6d %10lf" % (i*5, cnt[i], cnt[i] * 100.0 / s)
    elif type == 'cdf':
      cnt = sorted(cnt.iteritems(), key=operator.itemgetter(0))
      c = 0
      for kv in cnt:
        c += kv[1]
        print "%10lf %6d %10lf" % (kv[0], c, c * 100.0 / s)
    print "Sum: %d" % s

  def get_all_finger_mdistr(self, type='anomaly', all_fig=False, per_second=False):
    if type == 'anomaly':
      sql = ''' SELECT alarm_id, sig, cnt01+cnt11, cnt00+cnt01+cnt10+cnt11, elapse_time
          FROM signature
          WHERE cnt00+cnt01+cnt10+cnt11 > 0
          ORDER BY alarm_id '''
    elif type == 'alarm':
      if not all_fig:
        sql = ''' SELECT alarm_id, sig, cnt01+cnt11, cnt00+cnt01+cnt10+cnt11, elapse_time
            FROM signature
            WHERE cnt00+cnt01+cnt10+cnt11 > 0 AND sig >= 0.6
            ORDER BY alarm_id '''
      else:
        sql = ''' SELECT alarm_id, sig, cnt01+cnt11, cnt00+cnt01+cnt10+cnt11, elapse_time
            FROM signature
            WHERE cnt00+cnt01+cnt10+cnt11 > 0 AND EXISTS (
              SELECT * FROM alarm
              WHERE alarm.alarm_id = signature.alarm_id AND is_hijacking != '')
            ORDER BY alarm_id '''
    elif type == 'salarm':
      if not all_fig:
        sql = ''' SELECT alarm_id, sig, cnt01+cnt11, cnt00+cnt01+cnt10+cnt11, elapse_time
            FROM signature
            WHERE cnt00+cnt01+cnt10+cnt11 > 0 AND sig >= 0.6 AND EXISTS (
              SELECT * FROM alarm
              WHERE alarm.alarm_id = signature.alarm_id AND is_hijacking = 'Y')
            ORDER BY alarm_id '''
      else:
        sql = ''' SELECT alarm_id, sig, cnt01+cnt11, cnt00+cnt01+cnt10+cnt11, elapse_time
            FROM signature
            WHERE cnt00+cnt01+cnt10+cnt11 > 0 AND EXISTS (
              SELECT * FROM alarm
              WHERE alarm.alarm_id = signature.alarm_id AND is_hijacking = 'Y')
            ORDER BY alarm_id '''
    self.cursor.execute(sql)
    aid = -1
    max_live = 0.
    sig = []
    for r in self.cursor.fetchall():
      if per_second:
        print >> sys.stderr, "%f %f %d" % (r[1], r[2] * 1. / r[3], r[4])
      if r[0] != aid:
        if sig:
          sig.sort()
          s = 0
          for v in sig:
            s += v
          print "%f %f %f %f %f" % (sig[0], s / len(sig), sig[-1],
              sig[len(sig) / 2], max_live)
          max_live = 0.
          del sig[:]
        aid = r[0]
      sig.append(r[1])
      if max_live < r[2] * 1. / r[3]:
        max_live = r[2] * 1. / r[3]
    if sig:
      sig.sort()
      s = 0
      for v in sig:
        s += v
      print "%f %f %f %f %f" % (sig[0], s / len(sig), sig[-1],
          sig[len(sig) / 2], max_live)

  def get_all_finger_distr(self):
    #sql = ''' SELECT MIN(sig), AVG(sig), MAX(sig), MAX(cnt01+cnt11), MAX(cnt00+cnt01+cnt10+cnt11)
    #    FROM signature
    #    WHERE cnt00+cnt01+cnt10+cnt11 > 20
    #    GROUP BY alarm_id
    #    ORDER BY MAX(sig) DESC
    #  '''
    #self.cursor.execute(sql)
    #for r in self.cursor.fetchall():
    #  print "%f %f %f %f" % (r[0], r[1], r[2], r[3] * 1. / r[4])
    sql = ''' SELECT sig, (cnt01+cnt11)/(cnt00+cnt01+cnt10+cnt11)
        FROM signature
        WHERE cnt00+cnt01+cnt10+cnt11 > 20
      '''
    self.cursor.execute(sql)
    for r in self.cursor.fetchall():
      print "%f %f" % (r[0], r[1])

  def get_fnr_vs_s(self, crit='rpki', mu=0.6, output_T=-1):
    if crit == 'rpki':
      sql = ''' SELECT count(*) FROM alarm WHERE rpki = 'N' AND time <= %d ''' % END_TIME
      self.cursor.execute(sql)
      total = self.cursor.fetchall()[0][0]
      sql = ''' SELECT alarm_id FROM alarm
          WHERE is_hijacking != '' AND rpki = 'N' AND time <= %d ''' % END_TIME
    elif crit == 'whois':
      sql = ''' SELECT count(*) FROM alarm
          WHERE whois_origin = 1 AND time <= %d AND EXISTS (
            SELECT * FROM signature WHERE signature.alarm_id = alarm.alarm_id) ''' % END_TIME
      self.cursor.execute(sql)
      total = self.cursor.fetchall()[0][0]
      sql = ''' SELECT alarm_id FROM alarm
          WHERE is_hijacking != '' AND whois_origin = 1 AND time <= %d AND EXISTS (
            SELECT * FROM signature WHERE signature.alarm_id = alarm.alarm_id) ''' % END_TIME
    self.cursor.execute(sql)
    alarms = []
    for r in self.cursor.fetchall():
      alarms.append(r[0])
    fig_last = {}
    for al in alarms:
      sql = ''' SELECT elapse_time, sig FROM signature
          WHERE alarm_id = %s ORDER BY elapse_time '''
      self.cursor.execute(sql, (al, ))
      pt = -1
      fl = 0
      for r in self.cursor.fetchall():
        if r[0] >= 120:
          break
        if pt != -1:
          fl += r[0] - pt
          pt = -1
        if r[1] >= mu:
          pt = r[0]
      if pt != -1:
        fl += 120 - pt
      if fl in fig_last:
        fig_last[fl] += 1
      else:
        fig_last[fl] = 1
      if output_T == -1:
        print "ID %d SIG_LAST %lf" % (al, fl)
    fig_last = sorted(fig_last.iteritems(),
        reverse=True, key=operator.itemgetter(0))
    s = 0
    p = 120
    for i in xrange(len(fig_last)):
      fl = fig_last[i]
      for j in xrange(p, fl[0], -1):
        if output_T == -1 or j == output_T:
          print "%10lf %10lf %3d %10lf" % (mu, j, s, s * 100. / total, )
      p = fl[0]
      s += fl[1]
    for j in xrange(p, 0, -1):
      if output_T == -1 or j == output_T:
        print "%10lf %10lf %3d %10lf" % (mu, j, s, s * 100. / total, )
    if output_T == -1:
      print "All: %d" % len(alarms)

  def get_alarm_s_dist(self, mu=0.6):
    sql = ''' SELECT alarm.alarm_id, MAX(sig) FROM alarm, signature
        WHERE alarm.alarm_id = signature.alarm_id AND alarm.time <= %d
        GROUP BY alarm.alarm_id
        HAVING MAX(sig) >= %s
      ''' % (END_TIME, mu)
    self.cursor.execute(sql)
    alarms = []
    for r in self.cursor.fetchall():
      alarms.append(r[0])
    fig_last = {}
    for al in alarms:
      sql = ''' SELECT elapse_time, sig FROM signature
          WHERE alarm_id = %s ORDER BY elapse_time '''
      self.cursor.execute(sql, (al, ))
      pt = -1
      fl = 0
      for r in self.cursor.fetchall():
        if r[0] >= 120:
          break
        if pt != -1:
          fl += r[0] - pt
          pt = -1
        if r[1] >= mu:
          pt = r[0]
      if pt != -1:
        fl += 120 - pt
      if fl in fig_last:
        fig_last[fl] += 1
      else:
        fig_last[fl] = 1
      print "ID %d SIG_LAST %lf" % (al, fl)
    fig_last = sorted(fig_last.iteritems(),
        key=operator.itemgetter(0))
    s = 0
    for fl in fig_last:
      s += fl[1]
      print "%10lf %3d %10lf" % (fl[0], s, s * 100. / len(alarms), )
    print "All: %d" % len(alarms)

  def get_fingerprint(self):
    aid = input("Enter Alarm ID: ")
    sql = ''' SELECT elapse_time, sig, cnt00+cnt01 AS cnt0,
          cnt01+cnt11 AS cnt1,
          cnt01+cnt11+cnt00+cnt10 AS cnt FROM signature
        WHERE alarm_id = %s ORDER BY elapse_time '''
    self.cursor.execute(sql, (aid, ))
    sig = [0] * 121
    cnt0 = [0] * 121
    cnt1 = [0] * 121
    cnt = [0] * 121
    first = -1
    for r in self.cursor.fetchall():
      if r[0] >= 120:
        break
      if first == -1:
        first = r[0]
      for i in xrange(r[0], 120):
        sig[i] = r[1]
        cnt0[i] = r[2]
        cnt1[i] = r[3]
        cnt[i] = r[4]
    for i in xrange(first, 120):
      print "%3d %10lf %3d %3d %3d" % (i, sig[i], cnt0[i], cnt1[i], cnt[i]),
      if cnt[i]:
        print "%10lf" % (cnt1[i] * 1. / cnt[i]),
      print

  def get_prefix_origin(self):
    def pfx_str2binrange(pfx_str, is_ipv6):
      '''
        convert human readable ip prefix to binary format NETWORK/BROADCAST range
      '''
      def uint2bin(u):
        ''' convert unsigned int to binary format '''
        return struct.pack('>I', u)
      def u1282bin(u128):
        ''' convert unsigned int-128 to binary format '''
        return struct.pack('>Q', (u128 >> 64)) + struct.pack(
            '>Q', (u128 & ((1 << 64) - 1)))
      def bin2uint(b):
        ''' convert 4-bytes binary to unsigned int '''
        return struct.unpack('>I', b)[0]
      def bin2u128(b):
        ''' convert 16-bytes binary to unsigned int128 '''
        return ((bin2ulong(b[:8]) << 64) | bin2ulong(b[8:]))
      def ip_str2bin(ip_str, is_ipv6):
        ''' convert human readable format ip address to 4/16-bytes binary format '''
        try:
          if not is_ipv6:
            return socket.inet_pton(socket.AF_INET, ip_str)
          else:
            if '.' in ip_str: # ::ffff:a.b.c.d
              return socket.inet_pton(socket.AF_INET, ip_str[7:])
            return socket.inet_pton(socket.AF_INET6, ip_str)
        except socket.error as e:
          return ''
      ip_str, psize = pfx_str.split('/')
      psize = int(psize)
      network_bin = ip_str2bin(ip_str, is_ipv6)
      if len(network_bin) == 4:
        network_int = bin2uint(network_bin)
        mask = ((1 << (32 - psize)) - 1)
        broadcast_bin = uint2bin(network_int | mask)
      elif len(network_bin) == 16:
        network_int = bin2u128(network_bin)
        #network_high_int = bin2ulong(network_bin[:8])
        #network_low_int = bin2ulong(network_bin[8:])
        mask = ((1 << (128 - psize)) - 1)
        #broadcast_high_bin = ulong2bin(network_high_int | (mask >> 64))
        #broadcast_low_bin = ulong2bin(network_low_int | (mask & ((1 << 64) - 1)))
        #broadcast_bin = broadcast_high_bin + broadcast_low_bin
        broadcast_bin = u1282bin(network_int | mask)
      else:
        psize = 0 # prefix can not convert
        broadcast_bin = network_bin
      return (network_bin, broadcast_bin, psize)
    prefix_dec = raw_input("Enter Prefix: ")
    ipbeg_bin, ipend_bin, psize = pfx_str2binrange(prefix_dec, False)
    sql = ''' SELECT prefix_dec, monip_dec, monas, homeas,
        firstseen, lastseen, uptime, isup FROM origin
        WHERE ipbeg <= %s AND pfx_length <= %s AND ipend >= %s
        ORDER BY pfx_length, prefix_dec, homeas, lastseen, firstseen '''
    self.cursor.execute(sql, (ipbeg_bin, psize, ipend_bin))
    for r in self.cursor.fetchall():
      print "%30s %30s %10d %10d %10d %10d %10d %d" % (
          r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7])

  def get_no_polluted_eye(self):
    # eye ASN
    sql = ''' SELECT eye_id, asn FROM eye '''
    self.cursor.execute(sql)
    eyes = self.cursor.fetchall()
    print '#eyes:', len(eyes)
    eye_asn = [0] * 500
    for e in eyes:
      eye_asn[e[0]] = e[1]
    # for all alarms
    sql = ''' SELECT alarm_id, srv_id
        FROM route AS r WHERE EXISTS (
          SELECT * FROM probe AS p
          WHERE r.alarm_id = p.alarm_id AND r.srv_id = p.srv_id
        ) ORDER BY alarm_id '''
    self.cursor.execute(sql)
    all_alarms = self.cursor.fetchall()
    print '#entries:', len(all_alarms)
    total_alarms = 0
    all_num_as = [0] * 100
    paid = -1
    all_asn = set()
    for a in all_alarms:
      if paid != a[0]:
        if paid != -1:
          all_num_as[len(all_asn)] += 1
          total_alarms += 1
        paid = a[0]
        all_asn = set()
      all_asn.add(eye_asn[a[1]])
    if paid != -1:
      all_num_as[len(all_asn)] += 1
      total_alarms += 1
    print '#total-alarms:', total_alarms
    # for no-polluted-eye alarms
    sql = ''' SELECT alarm_id FROM alarm AS a
        WHERE time <= %d AND EXISTS (
          SELECT * FROM signature AS s
          WHERE s.alarm_id = a.alarm_id
        )
        AND NOT EXISTS (
          SELECT * FROM signature AS s
          WHERE s.alarm_id = a.alarm_id AND s.cnt00 + s.cnt01 > 0
        ) ''' % END_TIME
    self.cursor.execute(sql)
    alarms = self.cursor.fetchall()
    print '#no-polluted-eye-alarms:', len(alarms)
    num_as = [0] * 100
    for aid in alarms:
      sql = ''' SELECT DISTINCT srv_id FROM route
          WHERE alarm_id = %d ''' % aid[0]
      self.cursor.execute(sql)
      srvs = self.cursor.fetchall()
      srv_id = set()
      for s in srvs:
        srv_id.add(s[0])
      sql = ''' SELECT DISTINCT srv_id FROM probe
          WHERE alarm_id = %d ''' % aid[0]
      self.cursor.execute(sql)
      srvs = self.cursor.fetchall()
      srv_id2 = set()
      for s in srvs:
        if s[0] in srv_id:
          srv_id2.add(s[0])
      asn = set()
      for s in srv_id2:
        asn.add(eye_asn[s])
      num_as[len(asn)] += 1
    s0, s1 = 0, 0
    for i in xrange(len(num_as)):
      if num_as[i]:
        s0 += num_as[i]
        s1 += all_num_as[i]
        print '%3d\t%10d\t%10d\t%10lf\t%10d\t%10d\t%10lf' % (i,
            num_as[i], all_num_as[i], num_as[i] * 1.0 / all_num_as[i],
            s0, s1, s0 * 1.0 / s1)

  def get_num_subprefix_hij(self):
    sql = ''' SELECT COUNT(*) FROM alarm
        WHERE time <= %d AND type IN (
          2, 3, 7, 8, 11, 12, 15, 16) ''' % END_TIME
    self.cursor.execute(sql)
    print "TOTAL: ", self.cursor.fetchall()
    sql = ''' SELECT COUNT(*) FROM alarm
        WHERE time <= %d AND is_hijacking != '' AND type IN (
          2, 3, 7, 8, 11, 12, 15, 16) ''' % END_TIME
    self.cursor.execute(sql)
    print "SUSPICIOUS: ", self.cursor.fetchall()
    sql = ''' SELECT COUNT(*) FROM alarm
        WHERE time <= %d AND is_hijacking = 'Y' AND type IN (
          2, 3, 7, 8, 11, 12, 15, 16) ''' % END_TIME
    self.cursor.execute(sql)
    print "HIJACK: ", self.cursor.fetchall()

  def get_bgpmon_with_loop(self):
    sql = ''' SELECT DISTINCT alarm_id, path FROM bgpmon
        WHERE time > 1331797631 AND EXISTS (
          SELECT * FROM alarm
          WHERE alarm.alarm_id = bgpmon.alarm_id AND is_hijacking = 'Y') '''
    self.cursor.execute(sql)
    for r in self.cursor.fetchall():
      asp = r[1].split(' ')
      ass = set()
      prev = ""
      for a in asp:
        if a == prev:
          continue
        prev = a
        if a in ass:
          print "%8d %s" % (r[0], r[1], )
        ass.add(a)

  def update_whois_origin(self):
    # alarm 4109 , 4220 is should not be set TODO
    sql = ''' UPDATE alarm set whois_origin = 0 '''
    self.cursor.execute(sql)
    #wf = open('./whois_origin')
    #for line in wf:
    #  cl = line[:-1].split('|')
    #  orig = cl[-1].split(' ')
    #  for o in orig:
    #    if o:
    #      if o[-1] == '*':
    #        o = o[2:-1]
    #      else:
    #        o = o[2:]
    #      if o:
    #        sql = ''' UPDATE alarm set whois_origin = 1
    #            WHERE homeas = %s AND prefix_dec = '%s' ''' % (o, cl[1])
    #        self.cursor.execute(sql)
    #wf.close()
    wf = open('./irr_origin')
    for line in wf:
      cl = line[:-1].split(' ')
      origs = set()
      for a in cl[1:]:
        origs.add(a)
      str_o = ', '.join(origs)
      for a in cl[1:]:
        sql = ''' UPDATE alarm set whois_origin = 1
            WHERE homeas = %s AND prefix_dec = '%s'
              AND oldhomeas NOT IN (''' % (a, cl[0]) + str_o + ')'
        self.cursor.execute(sql)
    # fix
    sql = ''' UPDATE alarm set whois_origin = 0 
        WHERE alarm_id = 4109 or alarm_id = 4220 '''
    self.cursor.execute(sql)
    wf.close()

  def close(self):
    self.cursor.close()
    self.conn.close()

if __name__ == '__main__':
  db = DBMgr()
  # eval
  # fnr
  ###new###
  #db.count_alarm()
  #db.count_visitor()
  #db.count_reportcnt()
  #db.calc_pollute_AS()
  #########
  #db.get_fnr_vs_s('whois', 0.6,)
  #for mu in [0.6, 0.7, 0.8, 0.9]:
  # db.get_alarm_s_dist(mu)
  #db.get_fnr_vs_mu('rpki', 1)
  #db.get_fnr_vs_eye('whois',1)
  #db.get_salarm_finger_distr('cdf')
  #db.get_alarm_finger_distr('cdf')
  #db.get_all_finger_distr()
  #db.get_all_finger_mdistr('salarm', True, False)
  #db.get_not_reply()
  # type
  #db.get_anomaly_time_type('pdf', 'month')
  db.get_alarm_time_type('cdf', 'month')
  #db.get_stable_alarm_time_type('cdf', 'week')
  #db.get_salarm_vs_neye()
  #db.get_alarm_vs_neye()
  #db.get_alarm_prefix_type()
  #db.get_alarm_prefix_length()
  #db.get_all_prefix_length()
  #db.get_n_prefix()
  #db.get_victim()
  # delay
  #db.get_alarm_delay()
  #db.get_stable_alarm_delay()
  # case
  # fingerprint
  #db.get_fingerprint()
  #db.get_bgpmon_with_loop()
  # origin
  #db.get_prefix_origin()
  #db.update_whois_origin()
  # extra
  #db.get_no_polluted_eye()
  #db.get_num_subprefix_hij()
  #db.get_inconsis_eye_in_AS()
  #db.get_inconsis_CD()
  #db.get_zerod_Ft()
  # maintain
  #db.update_visitor()
  db.close()
  sys.exit()

